<?php declare(strict_types=1);

namespace Shopware\Core\Framework\DataAbstractionLayer\Dbal;

use Doctrine\DBAL\ArrayParameterType;
use Doctrine\DBAL\Connection;
use Shopware\Core\Defaults;
use Shopware\Core\Framework\Context;
use Shopware\Core\Framework\DataAbstractionLayer\DataAbstractionLayerException;
use Shopware\Core\Framework\DataAbstractionLayer\Dbal\Exception\UnmappedFieldException;
use Shopware\Core\Framework\DataAbstractionLayer\Dbal\FieldResolver\FieldResolverContext;
use Shopware\Core\Framework\DataAbstractionLayer\EntityDefinition;
use Shopware\Core\Framework\DataAbstractionLayer\Field\AssociationField;
use Shopware\Core\Framework\DataAbstractionLayer\Field\Field;
use Shopware\Core\Framework\DataAbstractionLayer\Field\FkField;
use Shopware\Core\Framework\DataAbstractionLayer\Field\Flag\Inherited;
use Shopware\Core\Framework\DataAbstractionLayer\Field\IdField;
use Shopware\Core\Framework\DataAbstractionLayer\Field\ManyToManyAssociationField;
use Shopware\Core\Framework\DataAbstractionLayer\Field\ReferenceVersionField;
use Shopware\Core\Framework\DataAbstractionLayer\Field\StorageAware;
use Shopware\Core\Framework\DataAbstractionLayer\Field\TranslatedField;
use Shopware\Core\Framework\DataAbstractionLayer\Field\VersionField;
use Shopware\Core\Framework\DataAbstractionLayer\Search\Criteria;
use Shopware\Core\Framework\DataAbstractionLayer\Search\CriteriaPartInterface;
use Shopware\Core\Framework\Log\Package;
use Shopware\Core\Framework\Uuid\Uuid;

/**
 * This class acts only as helper/common class for all dbal operations for entity definitions.
 * It knows how an association should be joined, how a parent-child inheritance should act, how translation chains work, ...
 *
 * @internal
 */
#[Package('framework')]
class EntityDefinitionQueryHelper
{
    final public const HAS_TO_MANY_JOIN = 'has_to_many_join';

    public static function escape(string $string): string
    {
        if (str_contains($string, '`')) {
            throw DataAbstractionLayerException::invalidIdentifier($string);
        }

        return '`' . $string . '`';
    }

    public static function columnExists(Connection $connection, string $table, string $column): bool
    {
        $exists = $connection->fetchOne(
            'SHOW COLUMNS FROM ' . self::escape($table) . ' WHERE `Field` LIKE :column',
            ['column' => $column]
        );

        return !empty($exists);
    }

    public static function columnIsNullable(Connection $connection, string $table, string $column): bool
    {
        $exists = $connection->fetchOne(
            'SHOW COLUMNS FROM ' . self::escape($table) . ' WHERE `Field` LIKE :column AND `Null` = "YES"',
            ['column' => $column]
        );

        return !empty($exists);
    }

    public static function tableExists(Connection $connection, string $table): bool
    {
        return !empty(
            $connection->fetchOne(
                'SHOW TABLES LIKE :table',
                [
                    'table' => $table,
                ]
            )
        );
    }

    /**
     * @return array<Field>
     */
    public static function getFieldsOfAccessor(EntityDefinition $definition, string $accessor, bool $resolveTranslated = true): array
    {
        $parts = explode('.', $accessor);
        if ($definition->getEntityName() === $parts[0]) {
            array_shift($parts);
        }

        $accessorFields = [];

        $source = $definition;

        foreach ($parts as $part) {
            if ($part === 'extensions') {
                continue;
            }

            $fields = $source->getFields();
            $field = $fields->get($part);

            // continue if the current part is not a real field to allow access on collections
            if (!$field) {
                continue;
            }

            if ($field instanceof TranslatedField && $resolveTranslated) {
                $source = $source->getTranslationDefinition();
                \assert($source instanceof EntityDefinition);
                $fields = $source->getFields();
                $accessorFields[] = $fields->get($part);

                continue;
            }

            if ($field instanceof TranslatedField && !$resolveTranslated) {
                $accessorFields[] = $field;

                continue;
            }

            $accessorFields[] = $field;

            if (!$field instanceof AssociationField) {
                break;
            }

            $source = $field->getReferenceDefinition();
            if ($field instanceof ManyToManyAssociationField) {
                $source = $field->getToManyReferenceDefinition();
            }
        }

        return \array_values(\array_filter($accessorFields));
    }

    /**
     * Resolves the definition of the provided field name.
     * For e.g:
     * `product.name` => returns ProductDefinition
     * `product.unit.shortCode` => returns UnitDefinition
     */
    public static function getAssociatedDefinition(EntityDefinition $rootDefinition, string $field): EntityDefinition
    {
        $fields = self::getFieldsOfAccessor($rootDefinition, $field, false);

        array_pop($fields);
        $field = array_pop($fields);

        if ($field === null) {
            return $rootDefinition;
        }

        if ($field instanceof ManyToManyAssociationField) {
            return $field->getToManyReferenceDefinition();
        }

        if ($field instanceof AssociationField) {
            return $field->getReferenceDefinition();
        }

        return $rootDefinition;
    }

    /**
     * Returns the field instance of the provided fieldName.
     *
     * @example
     *
     * fieldName => 'product.name'
     * Returns the (new TranslatedField('name')) declaration
     *
     * Allows additionally nested referencing
     *
     * fieldName => 'category.products.name'
     * Returns as well the above field definition
     */
    public static function getField(string $fieldName, EntityDefinition $definition, string $root, bool $resolveTranslated = true): ?Field
    {
        $original = $fieldName;
        $prefix = $root . '.';

        if (mb_strpos($fieldName, $prefix) === 0) {
            $fieldName = mb_substr($fieldName, mb_strlen($prefix));
        } else {
            $original = $prefix . $original;
        }

        $fields = $definition->getFields();

        $isAssociation = mb_strpos($fieldName, '.') !== false;

        if (!$isAssociation && $fields->has($fieldName)) {
            $field = $fields->get($fieldName);
        } else {
            $associationKey = explode('.', $fieldName);
            $associationKey = array_shift($associationKey);

            $field = $fields->get($associationKey);
        }

        if ($field instanceof TranslatedField && $resolveTranslated) {
            return self::getTranslatedField($definition, $field);
        }
        if ($field instanceof TranslatedField) {
            return $field;
        }

        if (!$field instanceof AssociationField) {
            return $field;
        }

        $referenceDefinition = $field->getReferenceDefinition();
        if ($field instanceof ManyToManyAssociationField) {
            $referenceDefinition = $field->getToManyReferenceDefinition();
        }

        return static::getField(
            $original,
            $referenceDefinition,
            $root . '.' . $field->getPropertyName(),
            $resolveTranslated
        );
    }

    /**
     * Builds the sql field accessor for the provided field.
     *
     * @example
     *
     * fieldName => product.taxId
     * root      => product
     * returns   => `product`.`tax_id`
     *
     * This function is also used for complex field accessors like JsonArray Field, JsonObject fields.
     * It considers the translation and parent-child inheritance.
     *
     * fieldName => product.name
     * root      => product
     * return    => COALESCE(`product.translation`.`name`,`product.parent.translation`.`name`)
     *
     * @throws UnmappedFieldException
     */
    public function getFieldAccessor(string $fieldName, EntityDefinition $definition, string $root, Context $context): string
    {
        $fieldName = str_replace('extensions.', '', $fieldName);

        $original = $fieldName;
        $prefix = $root . '.';

        if (str_starts_with($fieldName, $prefix)) {
            $fieldName = mb_substr($fieldName, mb_strlen($prefix));
        } else {
            $original = $prefix . $original;
        }

        $fields = $definition->getFields();
        if ($fields->has($fieldName)) {
            $field = $fields->get($fieldName);

            return $this->buildInheritedAccessor($field, $root, $definition, $context, $fieldName);
        }

        $parts = explode('.', $fieldName);
        $associationKey = array_shift($parts);

        if ($associationKey === 'extensions') {
            $associationKey = array_shift($parts);
        }

        if (!\is_string($associationKey) || !$fields->has($associationKey)) {
            throw DataAbstractionLayerException::unmappedField($original, $definition);
        }

        $field = $fields->get($associationKey);

        // case for json object fields, other fields has now same option to act with more point notations but hasn't to be an association field. E.g. price.gross
        if (!$field instanceof AssociationField && ($field instanceof StorageAware || $field instanceof TranslatedField)) {
            return $this->buildInheritedAccessor($field, $root, $definition, $context, $fieldName);
        }

        if (!$field instanceof AssociationField) {
            throw DataAbstractionLayerException::unexpectedFieldType($associationKey, AssociationField::class);
        }

        $referenceDefinition = $field->getReferenceDefinition();
        if ($field instanceof ManyToManyAssociationField) {
            $referenceDefinition = $field->getToManyReferenceDefinition();
        }

        return $this->getFieldAccessor(
            $original,
            $referenceDefinition,
            $root . '.' . $field->getPropertyName(),
            $context
        );
    }

    public static function getAssociationPath(string $accessor, EntityDefinition $definition): ?string
    {
        $fields = self::getFieldsOfAccessor($definition, $accessor);

        $path = [];
        foreach ($fields as $field) {
            if (!$field instanceof AssociationField) {
                break;
            }
            $path[] = $field->getPropertyName();
        }

        if (empty($path)) {
            return null;
        }

        return implode('.', $path);
    }

    public static function getRoot(string $accessor, EntityDefinition $definition): ?string
    {
        $association = self::getAssociationPath($accessor, $definition);

        return $association ? explode('.', $association)[0] : null;
    }

    /**
     * Creates the basic root query for the provided entity definition and application context.
     * It considers the current context version.
     */
    public function getBaseQuery(QueryBuilder $query, EntityDefinition $definition, Context $context): QueryBuilder
    {
        $table = $definition->getEntityName();

        $query->from(self::escape($table));

        $useVersionFallback // only applies for versioned entities
            = $definition->isVersionAware()
            // only add live fallback if the current version isn't the live version
            && $context->getVersionId() !== Defaults::LIVE_VERSION
            // sub entities have no live fallback
            && $definition->getParentDefinition() === null;

        if ($useVersionFallback) {
            $this->joinVersion($query, $definition, $definition->getEntityName(), $context);
        } elseif ($definition->isVersionAware()) {
            $versionIdField = array_filter(
                $definition->getPrimaryKeys()->getElements(),
                fn ($f) => $f instanceof VersionField || $f instanceof ReferenceVersionField
            );

            if (!$versionIdField) {
                throw DataAbstractionLayerException::missingVersionField($definition->getClass());
            }

            $versionIdField = array_shift($versionIdField);

            $query->andWhere(self::escape($table) . '.' . self::escape($versionIdField->getStorageName()) . ' = :version');
            $query->setParameter('version', Uuid::fromHexToBytes($context->getVersionId()));
        }

        return $query;
    }

    /**
     * Used for dynamic sql joins. In case that the given fieldName is unknown or event nested with multiple association
     * roots, the function can resolve each association part of the field name, even if one part of the fieldName contains a translation or event inherited data field.
     */
    public function resolveAccessor(
        string $accessor,
        EntityDefinition $definition,
        string $root,
        QueryBuilder $query,
        Context $context,
        ?CriteriaPartInterface $criteriaPart = null
    ): void {
        $accessor = str_replace('extensions.', '', $accessor);

        $parts = explode('.', $accessor);

        if ($parts[0] === $root) {
            unset($parts[0]);
        }

        $alias = $root;

        $path = [$root];

        $rootDefinition = $definition;

        foreach ($parts as $part) {
            $field = $definition->getFields()->get($part);

            if ($field === null) {
                return;
            }

            $resolver = $field->getResolver();
            if ($resolver === null) {
                continue;
            }

            if ($field instanceof AssociationField) {
                $path[] = $field->getPropertyName();
            }

            $currentPath = implode('.', $path);
            $resolverContext = new FieldResolverContext($currentPath, $alias, $field, $definition, $rootDefinition, $query, $context, $criteriaPart);

            $alias = $this->callResolver($resolverContext);

            if (!$field instanceof AssociationField) {
                return;
            }

            $definition = $field->getReferenceDefinition();
            if ($field instanceof ManyToManyAssociationField) {
                $definition = $field->getToManyReferenceDefinition();
            }

            if ($definition->isInheritanceAware() && $context->considerInheritance() && $parent = $definition->getField('parent')) {
                $resolverContext = new FieldResolverContext($currentPath, $alias, $parent, $definition, $rootDefinition, $query, $context, $criteriaPart);

                $this->callResolver($resolverContext);
            }
        }
    }

    public function resolveField(Field $field, EntityDefinition $definition, string $root, QueryBuilder $query, Context $context): void
    {
        $resolver = $field->getResolver();

        if ($resolver === null) {
            return;
        }

        $resolver->join(new FieldResolverContext($root, $root, $field, $definition, $definition, $query, $context, null));
    }

    /**
     * Adds the full translation select part to the provided sql query.
     * Considers the parent-child inheritance and provided context language inheritance.
     * The raw parameter allows to skip the parent-child inheritance.
     *
     * @param array<string, mixed> $partial
     */
    public function addTranslationSelect(string $root, EntityDefinition $definition, QueryBuilder $query, Context $context, array $partial = []): void
    {
        $translationDefinition = $definition->getTranslationDefinition();

        if (!$translationDefinition) {
            return;
        }

        $fields = $translationDefinition->getFields()->filter(
            fn (Field $field) => $field instanceof StorageAware
                && $definition->getFields()->get($field->getPropertyName()) instanceof TranslatedField,
        );
        if (!empty($partial)) {
            $fields = $fields->filter(fn (Field $field) => isset($partial[$field->getPropertyName()]));
        }

        $translationChain = self::buildTranslationChain(
            $root,
            $context,
            includeParent: $definition->isInheritanceAware() && $context->considerInheritance(),
        );

        foreach ($fields as $field) {
            $selects = [];
            foreach ($translationChain as $select) {
                $vars = [
                    '#root#' => $select,
                    '#field#' => $field->getPropertyName(),
                ];
                $select = str_replace(
                    array_keys($vars),
                    array_values($vars),
                    self::escape('#root#.#field#'),
                );

                $query->addSelect($select);
                $selects[] = $select;
            }

            $selects = array_unique($selects);

            if (\count($selects) === 1) {
                $query->addSelect(\sprintf('%s as %s', $selects[0], self::escape($root . '.' . $field->getPropertyName())));

                continue;
            }

            // add selection for resolved parent-child and language inheritance
            $query->addSelect(
                \sprintf('COALESCE(%s)', implode(',', $selects)) . ' as '
                . self::escape($root . '.' . $field->getPropertyName())
            );
        }
    }

    public function joinVersion(QueryBuilder $query, EntityDefinition $definition, string $root, Context $context): void
    {
        $table = $definition->getEntityName();
        $versionRoot = $root . '_version';

        $query->andWhere(
            str_replace(
                ['#root#', '#table#', '#version#'],
                [self::escape($root), self::escape($table), self::escape($versionRoot)],
                '#root#.version_id = COALESCE(
                    (SELECT DISTINCT version_id FROM #table# AS #version# WHERE #version#.`id` = #root#.`id` AND `version_id` = :version),
                    :liveVersion
                )'
            )
        );

        $query->setParameter('liveVersion', Uuid::fromHexToBytes(Defaults::LIVE_VERSION));
        $query->setParameter('version', Uuid::fromHexToBytes($context->getVersionId()));
    }

    public static function getTranslatedField(EntityDefinition $definition, TranslatedField $translatedField): Field
    {
        $translationDefinition = $definition->getTranslationDefinition();

        if ($translationDefinition === null) {
            throw DataAbstractionLayerException::noTranslationDefinition($definition->getEntityName());
        }

        $field = $translationDefinition->getFields()->get($translatedField->getPropertyName());

        if (!$field instanceof StorageAware) {
            throw DataAbstractionLayerException::missingTranslatedStorageAwareProperty(
                $translatedField->getPropertyName(),
                $translationDefinition->getEntityName()
            );
        }

        return $field;
    }

    /**
     * @return list<string>
     */
    public static function buildTranslationChain(string $root, Context $context, bool $includeParent): array
    {
        $chain = [];
        $count = \count($context->getLanguageIdChain()) - 1;

        for ($i = $count; $i >= 1; --$i) {
            $chain[] = $root . '.translation.override_' . $i;
            if ($includeParent) {
                $chain[] = $root . '.parent.translation.override_' . $i;
            }
        }

        $chain[] = $root . '.translation';
        if ($includeParent) {
            $chain[] = $root . '.parent.translation';
        }

        return $chain;
    }

    /**
     * @param Criteria<string|array<string, string>> $criteria
     */
    public function addIdCondition(Criteria $criteria, EntityDefinition $definition, QueryBuilder $query): void
    {
        $primaryKeys = $criteria->getIds();
        $primaryKeys = array_values($primaryKeys);

        if (empty($primaryKeys)) {
            return;
        }

        if (!\is_array($primaryKeys[0]) || \count($primaryKeys[0]) === 1) {
            $primaryKeyField = $definition->getPrimaryKeys()->first();
            if ($primaryKeyField instanceof IdField || $primaryKeyField instanceof FkField) {
                $primaryKeys = array_map(static function ($id) {
                    if (\is_array($id)) {
                        $shiftedId = array_shift($id);
                        \assert(\is_string($shiftedId));

                        return Uuid::fromHexToBytes($shiftedId);
                    }

                    return Uuid::fromHexToBytes($id);
                }, $primaryKeys);
            }

            if (!$primaryKeyField instanceof StorageAware) {
                throw DataAbstractionLayerException::primaryKeyNotStorageAware();
            }

            $query->andWhere(\sprintf(
                '%s.%s IN (:ids)',
                EntityDefinitionQueryHelper::escape($definition->getEntityName()),
                EntityDefinitionQueryHelper::escape($primaryKeyField->getStorageName())
            ));

            $query->setParameter('ids', $primaryKeys, ArrayParameterType::BINARY);

            return;
        }

        $this->addIdConditionWithOr($criteria, $definition, $query);
    }

    public function addGroupBy(EntityDefinition $definition, Criteria $criteria, Context $context, QueryBuilder $query, string $table): void
    {
        if ($criteria->getGroupFields()) {
            foreach ($criteria->getGroupFields() as $grouping) {
                $accessor = $this->getFieldAccessor($grouping->getField(), $definition, $definition->getEntityName(), $context);

                $query->addGroupBy($accessor);
            }

            return;
        }

        if ($query->hasState(self::HAS_TO_MANY_JOIN)) {
            $query->addGroupBy(
                self::escape($table) . '.' . self::escape('id')
            );
        }
    }

    private function callResolver(FieldResolverContext $context): string
    {
        $resolver = $context->getField()->getResolver();

        if (!$resolver) {
            return $context->getAlias();
        }

        return $resolver->join($context);
    }

    private function addIdConditionWithOr(Criteria $criteria, EntityDefinition $definition, QueryBuilder $query): void
    {
        $wheres = [];

        foreach ($criteria->getIds() as $primaryKey) {
            if (!\is_array($primaryKey)) {
                $primaryKey = ['id' => $primaryKey];
            }

            $where = [];

            foreach ($primaryKey as $propertyName => $value) {
                $field = $definition->getFields()->get($propertyName);

                if (!$field) {
                    throw DataAbstractionLayerException::unmappedField($propertyName, $definition);
                }

                if (!$field instanceof StorageAware) {
                    throw DataAbstractionLayerException::onlyStorageAwareFieldsInReadCondition();
                }

                if ($field instanceof IdField || $field instanceof FkField) {
                    $value = Uuid::fromHexToBytes($value);
                }

                $key = 'pk' . Uuid::randomHex();

                $accessor = EntityDefinitionQueryHelper::escape($definition->getEntityName()) . '.' . EntityDefinitionQueryHelper::escape($field->getStorageName());

                $where[$accessor] = $accessor . ' = :' . $key;

                $query->setParameter($key, $value);
            }

            $wheres[] = '(' . implode(' AND ', $where) . ')';
        }

        $wheres = implode(' OR ', $wheres);

        $query->andWhere($wheres);
    }

    /**
     * @param list<string> $chain
     */
    private function getTranslationFieldAccessor(Field $field, string $accessor, array $chain, Context $context): string
    {
        if (!$field instanceof StorageAware) {
            throw DataAbstractionLayerException::onlyStorageAwareFieldsAsTranslated();
        }

        $selects = [];
        foreach ($chain as $part) {
            $select = $this->buildFieldSelector($part, $field, $context, $accessor);

            $selects[] = str_replace(
                '`.' . self::escape($field->getStorageName()),
                '.' . $field->getPropertyName() . '`',
                $select
            );
        }

        /*
         * Simplified Example:
         * COALESCE(
             JSON_UNQUOTE(JSON_EXTRACT(`tbl.translation.override_2`.`translated_attributes`, '$.path')) AS datetime(3), # child language
             JSON_UNQUOTE(JSON_EXTRACT(`tbl.translation.override_1`.`translated_attributes`, '$.path')) AS datetime(3), # root language
             JSON_UNQUOTE(JSON_EXTRACT(`tbl.translation`.`translated_attributes`, '$.path')) AS datetime(3) # system language
           );
         */
        return \sprintf('COALESCE(%s)', implode(',', $selects));
    }

    private function buildInheritedAccessor(
        Field $field,
        string $root,
        EntityDefinition $definition,
        Context $context,
        string $original
    ): string {
        if ($field instanceof TranslatedField) {
            $translationChain = self::buildTranslationChain(
                $root,
                $context,
                includeParent: $definition->isInheritanceAware() && $context->considerInheritance(),
            );

            $translatedField = self::getTranslatedField($definition, $field);

            return $this->getTranslationFieldAccessor($translatedField, $original, $translationChain, $context);
        }

        $select = $this->buildFieldSelector($root, $field, $context, $original);

        if (!$field->is(Inherited::class) || !$context->considerInheritance()) {
            return $select;
        }

        $parentSelect = $this->buildFieldSelector($root . '.parent', $field, $context, $original);

        return \sprintf('IFNULL(%s, %s)', $select, $parentSelect);
    }

    private function buildFieldSelector(string $root, Field $field, Context $context, string $accessor): string
    {
        $accessorBuilder = $field->getAccessorBuilder();
        if (!$accessorBuilder) {
            throw DataAbstractionLayerException::fieldAccessorBuilderNotFound($field->getPropertyName());
        }

        $accessor = $accessorBuilder->buildAccessor($root, $field, $context, $accessor);

        if (!$accessor) {
            throw DataAbstractionLayerException::cannotBuildAccessor($field->getPropertyName(), $root);
        }

        return $accessor;
    }
}
